package com.zygh.questionnairesurvey.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.zygh.questionnairesurvey.entity.ProblemDetails;
import com.zygh.questionnairesurvey.entity.vo.DetailsStatica;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @program: questionnaire-survey
 * @description: 个人问题详情描述
 * @author: ID-Tang
 * @create: 2021-05-11 13:48
 **/
@Mapper
public interface ProblemDetailsMapper extends BaseMapper<ProblemDetails> {

    @Select("SELECT count(*) as count FROM problem_details p LEFT JOIN user_info u ON p.warningsignal = u.user_name " +
            " LEFT JOIN dept d  ON u.dept_id = d.dept_id\n" +
            "WHERE FIND_IN_SET(d.dept_id,getChildrenOrgOfHr(#{id}))")
    Integer findDetailByDeptId(Integer id);


    /**
     * 根据部门id导出问题统计人员信息
     *
     * @param id 部门id
     * @return
     */
    @Select("<script> SELECT d.pid,(SELECT name FROM user_info u WHERE u.user_name = q.warningsignal ) as name ,q.warningsignal,d.`name` as deptName\n" +
            "FROM questionnaire  q LEFT JOIN problem p ON FIND_IN_SET(p.id,q.lowest) \n" +
            "LEFT JOIN user_info u ON q.warningsignal=u.user_name  LEFT JOIN dept d ON q.dept_id=d.dept_id\n" +
            "<where> p.rank!=1 " +
            "<if test = 'termStatus != null and termStatus == 3 '> and u.term_status = #{termStatus} </if>" +
            "<if test = 'termStatus != null and termStatus != 3 '> and u.term_status != 3 </if>" +
            "<if test = 'id != null'> and FIND_IN_SET(d.dept_id,getChildrenOrgOfHr(#{id})) </if> " +
            "</where> GROUP BY u.user_name " +
            "ORDER BY deptName,d.dept_id ASC  </script>")
    List<DetailsStatica> findDetailStaticeById(@Param("id") Integer id,
                                               @Param("termStatus") Integer termStatus);

    /**
     * 根据部门id导出问题统计人员信息（无问题人）
     *
     * @param id 部门id
     * @return
     */
    @Select("<script> SELECT d.pid,d.name as deptName,q.warningsignal," +
            "(SELECT name FROM user_info u WHERE u.user_name = q.warningsignal ) as name FROM questionnaire q " +
            "LEFT JOIN user_info u ON u.user_name= q.warningsignal LEFT JOIN dept d ON q.dept_id = d.dept_id \n" +
            "<where> LENGTH(lowest) = 0 " +
            "<if test = 'id != null'> and FIND_IN_SET(d.dept_id,getChildrenOrgOfHr(#{id})) </if> " +
            "<if test = 'termStatus != null and termStatus == 3 '> and u.term_status = #{termStatus} </if>" +
            "<if test = 'termStatus != null and termStatus != 3 '> and u.term_status != 3 </if>" +
            "</where>  ORDER BY d.sort ASC </script>")
    List<DetailsStatica> findNoDetailStaticeById(@Param("id") Integer id,
                                                 @Param("termStatus") Integer termStatus);

    @Select("<script>" +
            "SELECT count(*) FROM problem_details pd " +
            "LEFT JOIN problem p ON pd.problem_id =  p.id \n" +
            "LEFT JOIN user_info ui ON pd.warningsignal = ui.user_name \n" +
            "LEFT JOIN dept d ON ui.dept_id = d.dept_id " +
            "WHERE 1 = 1 " +
            "<if test = 'null != encoder and encoder.length &gt; 0'>" +
            "AND p.encoder like CONCAT(#{encoder},'%') " +
            "</if>" +
            "<if test = 'null != userNameList and userNameList.size &gt; 0'>" +
            "AND pd.warningsignal IN " +
            "<foreach item='userNameList' collection='userNameList' index='index'  open='(' separator=',' close=')'>" +
            "#{userNameList} " +
            "</foreach>" +
            "</if>" +
            "<if test = 'null != termStatus and 3 == termStatus'>" +
            "AND ui.term_status = 3 " +
            "</if>" +
            "<if test = 'null != termStatus and 3 != termStatus'>" +
            "AND ui.term_status in (0,1) " +
            "</if>" +
            "</script>")
    int countByWrapper(@Param("encoder") String encoder,
                       @Param("termStatus") Integer termStatus,
                       @Param("userNameList") List<String> userNameList);
}
